package com.example.db;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.example.bean.GridViewBean;

public class DBService {
	
	private static final String TABLE_NAME ="zmyBill";
	private static final String BILL_USERNAME = "username";
	private static final String BILL_MONEY = "money";
	private static final String BILL_IMG = "img";
	private static final String BIll_TYPE = "type";
	private static final String BILL_STATE = "state";
	private static final String BILL_TIME = "time";
	private static final String BILL_MARK = "mark";
    private static final String TIME_FLAG = "timeflag";
	
	private GridViewBean mBean;
	private DatabaseHelper dbHelper;
	private SQLiteDatabase db;
	
	private static final String USER_TABLE = "zmyUser";
	private static final String USERNAME = "username";
	private static final String PASSWORD = "password";
	
	private static final String NOTE_TABLE = "zmyNote";
	private static final String NOTE_TITLE = "title";
	private static final String NOTE_CONTENT = "content";
	
	public DBService(Context context){
		dbHelper = new DatabaseHelper(context, "bill.db", null, 1);
		db = dbHelper.getReadableDatabase();
	}
	
	/**
	 * zmyUser表保存用户名跟密码
	 */
	public long insertUser(String username,String password){
		ContentValues values = new ContentValues();
		values.put(USERNAME, username);
		values.put(PASSWORD, password);
		long e = db.insert(USER_TABLE, null, values);
		return e;
	}
	
	/**
	 * zmyUser查找所有的用户名
	 */
	public List<String> findAllUser(){
		List<String> list = new ArrayList<String>();
		Cursor cursor = db.query(USER_TABLE, null, null, null, null, null, null);
		while(cursor.moveToNext()){
			String str = cursor.getString(cursor.getColumnIndex(USERNAME));
			list.add(str);
		}
		return list;
	}
	
	/**
	 * zmyUser 根据 用户名查找密码
	 */
	public String finaPass(String username){
		Cursor cursor = db.query(USER_TABLE, null, "username = ?",
                new String[]{username}, null, null, null);
		while(cursor.moveToNext()){
			String pass = cursor.getString(cursor.getColumnIndex(PASSWORD));
			return pass;
		}
		return null;
	}
	
	/**
	 * zmyUser 更新密码
	 */
	public long updatePass(String name,String pass){
		ContentValues values = new ContentValues();
		values.put(USERNAME, name);
		values.put(PASSWORD, pass);
		long num = db.update(USER_TABLE, values, "username = ?", new String[]{name});
		return num;
	}
	
	/**
	 * zmyBill 表中 插入账单记录
	 */
	public long insertBill(GridViewBean gvb){
		ContentValues values = new ContentValues();
		values.put(BILL_USERNAME, gvb.getUserName());
		values.put(BILL_MONEY, gvb.getMoney());
		values.put(BILL_IMG, gvb.getImg());
		values.put(BIll_TYPE, gvb.getType());
		values.put(BILL_STATE, gvb.getState());
		values.put(BILL_TIME, gvb.getTime());
		values.put(BILL_MARK, gvb.getMark());
        if(gvb.getTime().split("-").length>=2){
            String flag = gvb.getTime().split("-")[0]+gvb.getTime().split("-")[1];
            values.put(TIME_FLAG,flag);
            Log.d("DBLIST","insert timeFlag = " + flag);
        }
		long e = db.insert(TABLE_NAME, null, values);
		return e;
	}
	
	/**
	 * 更新bill表中的数据
	 */
	public long updateBill(String id,GridViewBean bean){
		ContentValues values = new ContentValues();
		values.put(BILL_USERNAME, bean.getUserName());
		values.put(BILL_MONEY, bean.getMoney());
		values.put(BILL_IMG, bean.getImg());
		values.put(BIll_TYPE, bean.getType());
		values.put(BILL_STATE, bean.getState());
		values.put(BILL_TIME, bean.getTime());
		values.put(BILL_MARK, bean.getMark());
		long e = db.update(TABLE_NAME, values, "id = ?", new String[]{id});
		return e;
	}
	
	/**
	 * 查找所有的bill 账单根据用户名
	 */
	public List<GridViewBean> findAllBillByName(String name){
		List<GridViewBean> list = new ArrayList<GridViewBean>();
		Cursor cursor = db.query(TABLE_NAME, null, "username = ?", new String[]{name}, null, null, "id desc");
		while(cursor.moveToNext()){
			int id = Integer.parseInt(cursor.getString(cursor.getColumnIndex("id")));
			String dbmoney = cursor.getString(cursor.getColumnIndex(BILL_MONEY));
			String dbimg = cursor.getString(cursor.getColumnIndex(BILL_IMG));
			String dbtype = cursor.getString(cursor.getColumnIndex(BIll_TYPE));
			String dbsta = cursor.getString(cursor.getColumnIndex(BILL_STATE));
			String dbtime = cursor.getString(cursor.getColumnIndex(BILL_TIME));
			String dbmark = cursor.getString(cursor.getColumnIndex(BILL_MARK));
			GridViewBean bean = new GridViewBean();
			bean.setId(id);
			bean.setMoney(dbmoney);
			bean.setImg(Integer.parseInt(dbimg));
			bean.setType(dbtype);
			bean.setState(dbsta);
			bean.setTime(dbtime);
			bean.setMark(dbmark);
			list.add(bean);
		}
		return list;
		
	}

    /**
     * 统计
     * @param name
     * @param state
     * @return
     */
    public HashMap<String,Float> findCountByName(String name,String state,String timeFlag){

        Cursor cursor = db.rawQuery("select " +BILL_TIME +","+ BIll_TYPE + ", sum(" + BILL_MONEY + ") as `total` from " + TABLE_NAME
                + " where " + BILL_STATE + "=? and username=? and "+TIME_FLAG+"=? group by " + BIll_TYPE, new String[]{state, name ,timeFlag});
        Log.d("DBLIST","findCountByName timeFlag = " + timeFlag);
        HashMap<String,Float> map = new HashMap<>();
        while(cursor.moveToNext()){
            String time = cursor.getString(cursor.getColumnIndex(BILL_TIME));
            String typeName = cursor.getString(cursor.getColumnIndex(BIll_TYPE));
            float total = cursor.getFloat(cursor.getColumnIndex("total"));
            map.put(typeName,total);
            Log.d("DBLIST","typeName = " + typeName + " total = "+total + " time = " + time);
        }
        return map;
    }

	
	/**
	 * 根据 type 与time查找
	 */
//	public GridViewBean findGVB(String type,String time){
//		Cursor cursor = db.query(TABLE_NAME, null, "type = ? and time = ?", new String[]{type,time},
//				null, null, null);
//		while (cursor.moveToNext()) {
//			int id = Integer.parseInt(cursor.getString(cursor.getColumnIndex("id")));
//			String nam = cursor.getString(cursor.getColumnIndex(BILL_USERNAME));
//			String mon = cursor.getString(cursor.getColumnIndex(BILL_MONEY));
//			int img = Integer.parseInt(cursor.getString(cursor.getColumnIndex(BILL_IMG)));
//			String ty = cursor.getString(cursor.getColumnIndex(BIll_TYPE));
//			String sta = cursor.getString(cursor.getColumnIndex(BILL_STATE));
//			String tim = cursor.getString(cursor.getColumnIndex(BILL_TIME));
//			String mk = cursor.getString(cursor.getColumnIndex(BILL_MARK));
//			mBean = new GridViewBean();
//			mBean.setId(id);
//			mBean.setUserName(nam);
//			mBean.setMoney(mon);
//			mBean.setImg(img);
//			mBean.setType(ty);
//			mBean.setState(sta);
//			mBean.setTime(tim);
//			mBean.setMark(mk);
//		}
//		return mBean;
//	}
	
	
	/**
	 * 删除 单条账单
	 */
	public long deleBill(String id){
		long e = db.delete(TABLE_NAME, "id = ?", new String[]{id});
		return e;
	}
	
	/**
	 * 插入 note到数据库中
	 */
	public long insertNote(String title,String content){
		ContentValues values = new ContentValues();
		values.put(NOTE_TITLE, title);
		values.put(NOTE_CONTENT, content);
		long e = db.insertOrThrow(NOTE_TABLE, null, values);
		return e;
	}
	
	/**
	 * 查找所有的note
	 */
	public void findAllNote(String id){
		Cursor cursor = db.query(NOTE_TABLE, null, "id = ?", new String[]{id}, 
				null,null,null,null);
		
	}
	
	public void offDB(){
		db.close();
		dbHelper.close();
	} 
}
